#include "sqlite3helper.h"
#include <iostream>
#include <cstdio>
#include <unistd.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include "sqlite3/sqlite3.h"

#if defined(WIN32) || defined(WIN64) || defined(_WIN32) || defined(_WIN64)
/***********************************************************************
 * function: delete file
 * input params:
 *      strFilePath: file path
 * *********************************************************************/
static void deleteFile(const std::string& strFilePath)
{
    // 确保文件存在才进行删除
    if (FILE* file = fopen(strFilePath.c_str(), "r")) {
        fclose(file);
        file = nullptr;
        std::remove(strFilePath.c_str());
    }

    return;
}
#endif

/***********************************************************************
 * function: constructor
 * *********************************************************************/
Sqlite3Helper::Sqlite3Helper()
    : m_pSqlite3Instance(nullptr)
    , m_strDbName("")
{
}

/**********************************************************************
 * function: destructor
 * *********************************************************************/
Sqlite3Helper::~Sqlite3Helper()
{
    closeDatabase();
}

/**********************************************************************
 * function: create or open database
 * input params:
 *      strDbName: database name
 * return:
 *      bool: operation result, true -- success, false -- failed
 * *********************************************************************/
bool Sqlite3Helper::createOrOpenDatabase(const std::string& strDbName)
{
    if (m_pSqlite3Instance)
    {
        printf("Sqlite3Helper::createOrOpenDatabase, please close last database first.\r\n");
        return false;
    }

    m_strDbName = strDbName;
    bool bRet = false;
    if (m_strDbName.empty())
    {
        printf("Sqlite3Helper::createOrOpenDatabase, database name is empty, please input datebase name first.\r\n");
    }
    else
    {
        if (m_mt4Business.try_lock())
        {
            if (SQLITE_OK != sqlite3_open_v2(m_strDbName.data(), &m_pSqlite3Instance
                                             , SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE
                                             | SQLITE_OPEN_NOMUTEX | SQLITE_OPEN_SHAREDCACHE
                                             , nullptr))
            {
                printf("Sqlite3Helper::createOrOpenDatabase, open database name %s failed.\r\n", m_strDbName.data());
            }
            else
            {
                bRet = true;
            }

            m_mt4Business.unlock();
        }
    }

    return bRet;
}

/**********************************************************************
 * function: close database
 * *********************************************************************/
void Sqlite3Helper::closeDatabase()
{
    if (m_pSqlite3Instance)
    {
        if (m_mt4Business.try_lock())
        {
            sqlite3_close_v2(m_pSqlite3Instance);
            m_pSqlite3Instance = nullptr;
            m_mt4Business.unlock();
        }
    }

    return;
}

/**********************************************************************
  * function: drop (delete) database
  * input params:
  *      strDbName: database name
  * return:
  *      bool: operation result, true -- success, false -- failed
  * *********************************************************************/
void Sqlite3Helper::dropDatabase(const std::string& strDbName)
{
    // 关闭可能存在的数据库连接
    sqlite3* db = nullptr;
    if (SQLITE_OK == sqlite3_open(strDbName.data(), &db)) {
        sqlite3_close(db);
        db = nullptr;
    }

    // 可能涉及到-wal（预写日志文件）、-shm（共享内存文件）
    char szWalFile[256] = {0};
    snprintf(szWalFile, sizeof(szWalFile), "%s-wal", strDbName.data());

    char szsShmFile[256] = {0};
    snprintf(szsShmFile, sizeof(szsShmFile), "%s-shm", strDbName.data());

#ifdef __linux__
    unlink(strDbName.data()); // 删除主数据库文件
    unlink(szWalFile); // 删除WAL文件
    unlink(szsShmFile); // 删除SHM文件
#else
    deleteFile(strDbName); // 删除主数据库文件
    deleteFile(szWalFile); // 删除WAL文件
    deleteFile(szsShmFile); // 删除SHM文件
#endif

    return;
}

/**********************************************************************
 * function: create table in this data base
 * input params:
 *      strTableName: the table name
 *      strExpression: sql expression
 * return:
 *      bool: operation result, true -- success, false -- failed
 * *********************************************************************/
bool Sqlite3Helper::createTable(const std::string& strTableName, const std::string& strExpression)
{
    bool bRet = false;
    if (m_pSqlite3Instance)
    {
        std::string strSqlStatement = "create table if not exists ";
        strSqlStatement += strTableName;
        strSqlStatement += " (";
        strSqlStatement += strExpression;
        strSqlStatement += ") ;";

        if (m_mt4Business.try_lock())
        {
            bRet = exec(strSqlStatement);
            m_mt4Business.unlock();
        }
    }

    return bRet;
}

/**********************************************************************
 * function: drop table in this data base
 * input params:
 *      strTableName: the table name
 * return:
 *      bool: operation result, true -- success, false -- failed
 * *********************************************************************/
bool Sqlite3Helper::dropTable(const std::string& strTableName)
{
    bool bRet = false;
    if (m_pSqlite3Instance)
    {
        //sqlite3_exec(m_pSqlite3Instance, "DROP TABLE IF EXISTS table1; DROP TABLE IF EXISTS table2;", nullptr, nullptr, nullptr); // example
        std::string strSqlStatement = "drop table if exists ";
        strSqlStatement += strTableName;
        strSqlStatement += ";";

        if (m_mt4Business.try_lock())
        {
            bRet = exec(strSqlStatement);
            m_mt4Business.unlock();
        }
    }

    return bRet;
}

/**********************************************************************
 * function: insert records into the table
 * input params:
 *      trTableName: the table name
 *      vtNames: record names
 *      vtValues: record values
 * return:
 *      bool: operation result, true -- success, false -- failed
 * *********************************************************************/
bool Sqlite3Helper::insert(const std::string& strTableName
                           , const std::vector<std::string>& vtNames
                           , const std::vector<std::string>& vtValues)
{
    if (vtNames.size() != vtNames.size())
    {
        printf("Sqlite3Helper::insert, name list size is not equal to value list size.\r\n");
        return false;
    }

    bool bRet = false;
    if (m_pSqlite3Instance)
    {
        std::string strSqlStatement = "insert into ";
        strSqlStatement += strTableName;
        strSqlStatement += " (";

        for (size_t i = 0, iSize = vtNames.size(); i < iSize; ++i)
        {
            strSqlStatement += vtNames[i];
            if (i == iSize - 1)
            {
                strSqlStatement += ")";
            }
            else
            {
                strSqlStatement += ", ";
            }
        }

        strSqlStatement += " values (";

        std::string strTmpInfo = "";
        for (size_t i = 0, iSize = vtValues.size(); i < iSize; ++i)
        {
            strSqlStatement += "'";
            strSqlStatement += vtValues[i];
            strSqlStatement += "'";
            if (i != iSize - 1)
            {
                strSqlStatement += ", ";
            }
        }

        strSqlStatement += ");";

        if (m_mt4Business.try_lock())
        {
            bRet = exec(strSqlStatement);
            m_mt4Business.unlock();
        }
    }

    return bRet;
}

/**********************************************************************
 * function: update records in the table
 * input params:
 *      strTableName: the table name
 *      vtNames: record names
 *      vtValues: record values
 *      strExpression: sql expression
 * return:
 *      bool: operation result, true -- success, false -- failed
 * *********************************************************************/
bool Sqlite3Helper::update(const std::string& strTableName
                           , const std::vector<std::string>& vtNames
                           , const std::vector<std::string>& vtValues
                           , const std::string& strExpression)
{
    if (vtNames.size() != vtNames.size())
    {
        printf("Sqlite3Helper::update, name list size is not equal to value list size.\r\n");
        return false;
    }

    bool bRet = false;
    if (m_pSqlite3Instance)
    {
        std::string strSqlStatement = "update ";
        strSqlStatement += strTableName;
        strSqlStatement += " set ";

        for (size_t i = 0, iSize = vtNames.size(); i < iSize; ++i)
        {
            strSqlStatement += vtNames[i];
            strSqlStatement += " = ";
            strSqlStatement += "'";
            strSqlStatement += vtValues[i];
            strSqlStatement += "'";
            if (i != iSize - 1)
            {
                strSqlStatement += ", ";
            }
        }

        strSqlStatement += " where ";
        strSqlStatement += strExpression;
        strSqlStatement += ";";

        if (m_mt4Business.try_lock())
        {
            bRet = exec(strSqlStatement);
            m_mt4Business.unlock();
        }
    }

    return bRet;
}

/**********************************************************************
 * function: delete record in the table
 * input params:
 *      strTableName: the table name
 *      strExpression: sql expression
 * return:
 *      bool: operation result, true -- success, false -- failed
 * *********************************************************************/
bool Sqlite3Helper::deleteRecord(const std::string& strTableName, const std::string& strExpression)
{
    bool bRet = false;
    if (m_pSqlite3Instance)
    {
        std::string strSqlStatement = "delete from ";
        strSqlStatement += strTableName;
        strSqlStatement += " where ";
        strSqlStatement += strExpression;
        strSqlStatement += ";";

        if (m_mt4Business.try_lock())
        {
            bRet = exec(strSqlStatement);
            m_mt4Business.unlock();
        }
    }

    return bRet;
}

/**********************************************************************
 * function: clear all records in the table
 * input params:
 *      strTableName: the table name
 * return:
 *      bool: operation result, true -- success, false -- failed
 * *********************************************************************/
bool Sqlite3Helper::clearRecords(const std::string& strTableName)
{
    bool bRet = false;
    if (m_pSqlite3Instance)
    {
        std::string strSqlStatement = "delete from ";
        strSqlStatement += strTableName;
        strSqlStatement += ";";

        if (m_mt4Business.try_lock())
        {
            bRet = exec(strSqlStatement);
            m_mt4Business.unlock();
        }
    }

    return bRet;
}

/**********************************************************************
 * function: get values in the table
 * input params:
 *      strTableName: the table name
 *      strExpression: sql expression
 *		iColumn: column of single row info
 * output params:
 *      qstrlstValues: query out the values
 * return:
 *      size_t: rows of query result
 * *********************************************************************/
size_t Sqlite3Helper::getValues(const std::string& strTableName, const std::string& strExpression
                                , size_t iColumn, std::vector<std::string>& vtValues)
{
    size_t iRows = 0;

    if (m_pSqlite3Instance) {
        std::string strSqlStatement = "select * from ";
        strSqlStatement += strTableName;
        if (!(strExpression.empty())) {
            strSqlStatement += " where ";
            strSqlStatement += strExpression;
        }
        strSqlStatement += ";";

        if (m_mt4Business.try_lock()) {
            sqlite3_stmt* pStmtHandler = nullptr; // stmt sentence handler
            // -1：auto length
            if (SQLITE_OK == sqlite3_prepare_v2(m_pSqlite3Instance, strSqlStatement.data(), -1, &pStmtHandler, nullptr)) {
                // execute the sentence
                while (SQLITE_ROW == sqlite3_step(pStmtHandler)) {
                    ++iRows;
                    // all column as to string info
                    for (size_t i = 0; i < iColumn; ++i) {
                        vtValues.emplace_back(std::string(reinterpret_cast<const char*>(sqlite3_column_text(pStmtHandler, static_cast<int>(i)))));
                    }
                }
            } else {
                printf("Sqlite3Helper::getValues, prepare sentence (%s) failed, error msg: %s.\r\n", strSqlStatement.data(), sqlite3_errmsg(m_pSqlite3Instance));
            }

            sqlite3_finalize(pStmtHandler); // clear handler, prepare next sentence;

            m_mt4Business.unlock();
        }
    }

    return iRows;
}

/**********************************************************************
 * function: get values by user sql statement, eg. slect * from {table name} where {limit express}
 * input params:
 *      strStatement: user sql statement sql
 *      iColumn：column of single row info
 * output params:
 *      vtResults: query out the values
 * return:
 *      size_t: rows of query result
 * *********************************************************************/
size_t Sqlite3Helper::getValuesByStatement(const std::string& strStatement, size_t iColumn, std::vector<std::string>& vtResults)
{
    size_t iRows = 0;

    if (m_pSqlite3Instance) {
        if (m_mt4Business.try_lock()) {
            sqlite3_stmt* pStmtHandler = nullptr; // stmt sentence handler
            // -1：auto length
            if (SQLITE_OK == sqlite3_prepare_v2(m_pSqlite3Instance, strStatement.data(), -1, &pStmtHandler, nullptr)) {
                // execute the sentence
                while (SQLITE_ROW == sqlite3_step(pStmtHandler)) {
                    ++iRows;
                    // all column as to string info
                    for (size_t i = 0; i < iColumn; ++i) {
                        //int iVal = sqlite3_column_int(pStmtHandler, i);
                        //double dVal = sqlite3_column_double(pStmtHandler, i);
                        vtResults.emplace_back(std::string(reinterpret_cast<const char*>(sqlite3_column_text(pStmtHandler, static_cast<int>(i)))));
                    }
                }
            } else {
                printf("Sqlite3Helper::getValuesByStatement, prepare sentence (%s) failed, error msg: %s.\r\n", strStatement.data(),  sqlite3_errmsg(m_pSqlite3Instance));
            }

            sqlite3_finalize(pStmtHandler); // clear handler, prepare next sentence;

            m_mt4Business.unlock();
        }
    }

    return iRows;
}

/**********************************************************************
 * function: get row count in the table
 * input params:
 *      strTableName: the table name
 * return:
 *      int: total count in the table
 * *********************************************************************/
size_t Sqlite3Helper::getRowCount(const std::string& strTableName)
{
    int iCount = 0;

    if (m_pSqlite3Instance){
#if 1
        char szStatement[128] = { 0 };
        snprintf(szStatement, sizeof (szStatement), "select * from %s;", strTableName.data());

        char** pResult = nullptr;
        char* pErrMsg = nullptr;
        int iColumn = 0;

        if (m_mt4Business.try_lock()) {
            if (SQLITE_OK == sqlite3_get_table(m_pSqlite3Instance, szStatement, &pResult, &iCount, &iColumn, &pErrMsg)) {
                sqlite3_free_table(pResult);
            } else {
                sqlite3_free(pErrMsg);
                printf("Sqlite3Helper::getCount, sqlite3_get_table execute failed.\r\n");
            }
            m_mt4Business.unlock();
        }
#else
        char szStatement[128] = { 0 };
        snprintf(szStatement, sizeof (szStatement), "select count(*) from %s;", strTableName.data());

        if (m_mt4Business.try_lock()) {
            sqlite3_stmt* pStmtHandler = nullptr; // stmt sentence handler
            // -1：auto length
            if (SQLITE_OK == sqlite3_prepare_v2(m_pSqlite3Instance, szStatement, -1, &pStmtHandler, nullptr)) {
                // execute the sentence
                while (SQLITE_ROW == sqlite3_step(pStmtHandler)){
                    ++iCount;
                }
            } else {
                printf("Sqlite3Helper::getCount, prepare sentence (%s) failed.\r\n", szStatement);
            }

            sqlite3_finalize(pStmtHandler); // clear handler, prepare next sentence;

            m_mt4Business.unlock();
        }
#endif
    }

    return static_cast<size_t>(iCount);
}

/**********************************************************************
 * function: backup record to new database
 * input params:
 *      strNewDbName: the new database name
 * return:
 *      bool: operation result, true -- success, false -- failed
 * *********************************************************************/
bool Sqlite3Helper::backup(const std::string& strNewDbName)
{
    bool bRet = false;

    sqlite3* pNewDb = nullptr;
    sqlite3_backup* pBackup = nullptr;

    if (SQLITE_OK == sqlite3_open(strNewDbName.data(), &pNewDb))
    {
        if (m_pSqlite3Instance)
        {
            if (m_mt4Business.try_lock())
            {
                // must main
                pBackup = sqlite3_backup_init(pNewDb, "main", m_pSqlite3Instance, "main");
                if (pBackup)
                {
                    int iRet = 0;
                    do
                    {
                        iRet = sqlite3_backup_step(pBackup, 10);
                        // progress
                        //sqlite3_backup_remaining(pBackup);
                        //sqlite3_backup_pagecount(pBackup);
                    } while (SQLITE_OK == iRet || SQLITE_BUSY == iRet || SQLITE_LOCKED == iRet);

                    sqlite3_backup_finish(pBackup);
                }

                m_mt4Business.unlock();
            }
        }
        bRet = SQLITE_OK == sqlite3_errcode(pNewDb) ? true : false;
        sqlite3_close(pNewDb);
    }
    else
    {
        printf("Sqlite3Helper::backup, open database %s failed.\r\n", strNewDbName.data());
    }

    return bRet;
}

/**********************************************************************
 * function: exec the sql statement
 * input params:
 *      strSqlStatement: the sql statement sentence
 * return:
 *      bool: operation result, true -- success, false -- failed
 * *********************************************************************/
bool Sqlite3Helper::exec(const std::string& strSqlStatement)
{
    bool bRet = false;
    if (m_pSqlite3Instance)
    {
        sqlite3_stmt* pStmtHandler = nullptr; // stmt sentence handler
        // -1：auto length
        if (SQLITE_OK == sqlite3_prepare_v2(m_pSqlite3Instance, strSqlStatement.data(), -1, &pStmtHandler, nullptr))
        {
            // execute the sentence
            //if (SQLITE_DONE == sqlite3_step(pStmtHandler));
            sqlite3_step(pStmtHandler);
            bRet = true;
        }
        else
        {
            printf("Sqlite3Helper::exec, prepare sentence (%s) failed.\r\n", strSqlStatement.data());
        }

        sqlite3_finalize(pStmtHandler); // clear handler, prepare next sentence;
    }

    return bRet;
}



